# -*- coding: utf-8 -*-

import os
import configparser
import pandas as pd
import sqlite3
import pymysql
from pyhive import hive
from EasyReportCode.easy.base.EncryptUtil import *

# DB_info_path = r"E:\P04_Python_Case_IRAHO\EasyReportCode\easy\base\DB_info.ini"
DB_info_path = os.path.join(os.path.dirname(__file__), 'DB_info.ini')

# # 简化版的sqlite数据库，使用这个方法
# class DB_Info():
#     """数据库链接信息封装对象"""
#     def __init__(self, dbtype, db):
#         self.dbtype = dbtype
#         self.db = db
#
#     def Get_SqliteDB_Info(Dsn):  # 获取数据库信息
#         """
#         通过链接字符串,获取数据库链接信息
#         :param Dsn 配置文件中数据库链接字符串信息
#         """
#         try:
#             if not os.path.exists(DB_info_path):
#                 raise Exception("配置文件'%s'不存在。"%DB_info_path)
#             config = configparser.ConfigParser()
#             config.read(DB_info_path)
#             di = DB_Info(config[Dsn]['dbtype'],
#                          config[Dsn]['db']
#                         )
#             return di
#         except KeyError as e:
#             # raise Exception("配置文件'%s'中没有找到相应的配置信息:%s"%(DB_info_path,e))
#             raise Exception(f"配置文件'{DB_info_path}'中没有找到相应的配置信息: {e}")
#         except Exception as e:
#             raise Exception(str(e))


# 标准版的sqlite3数据库使用这个方法
# 标准版的mysql数据库也使用这个方法，这个方法具备通用性
class DB_Info():
"""数据库链接信息封装对象"""
    def __init__(self, dbtype, host, dbport, dbuser, dbpassword, db, sshport, sshuser, sshpassword):
                self.dbtype = dbtype
                self.host = host
                self.dbport = dbport
                self.dbuser = dbuser
                self.dbpassword = dbpassword
                self.db = db
                self.sshport = sshport
                self.sshuser = sshuser
                self.sshpassword = sshpassword

# DB_info.ini未做加密处理，调用此方法
    def Get_DB_Info(Dsn,isdec=False):
        """
        通过链接字符串,获取数据库链接信息
        :param Dsn 配置文件中数据库链接字符串信息
        :param isdec 是否需要解密配置
        """
        try:
            if not os.path.exists(DB_info_path):
                raise Exception("配置文件'%s'不存在。"%DB_info_path)
            config = configparser.ConfigParser()
            config.read(DB_info_path)
            di = DB_Info(config[Dsn]['dbtype'],
                         config[Dsn]['host'],
                         config[Dsn]['dbport'],
                         config[Dsn]['dbuser'],
                         config[Dsn]['dbpassword'],
                         config[Dsn]['db'],
                         config[Dsn]['sshport'],
                         config[Dsn]['sshuser'],
                         config[Dsn]['sshpassword']
                         )
            return di
        except KeyError as e:
            # raise Exception("配置文件'%s'中没有找到相应的配置信息:%s"%(DB_info_path,e))
            raise Exception(f"配置文件'{DB_info_path}'中没有找到相应的配置信息: {e}")
        except Exception as e:
            raise Exception(str(e))

    # # DB_info.ini已做加密处理，调用此方法
    # def Get_DB_Info(Dsn,isdec=True):
    #     """
    #     通过链接字符串,获取数据库链接信息
    #     :param Dsn 配置文件中数据库链接字符串信息
    #     :param isdec 是否需要解密配置
    #     """
    #     try:
    #         if not os.path.exists(DB_info_path):
    #             raise Exception("配置文件'%s'不存在。"%DB_info_path)
    #         config = configparser.ConfigParser()
    #         config.read(DB_info_path)
    #         di = DB_Info(decstr(config[Dsn]['dbtype']) if isdec else config[Dsn]['dbtype'],
    #                             decstr(config[Dsn]['host']) if isdec else config[Dsn]['host'],
    #                              decstr(config[Dsn]['dbport']) if isdec else config[Dsn]['dbport'],
    #                              decstr(config[Dsn]['dbuser']) if isdec else config[Dsn]['dbuser'],
    #                              decstr(config[Dsn]['dbpassword']) if isdec else config[Dsn]['dbpassword'],
    #                              decstr(config[Dsn]['db']) if isdec else config[Dsn]['db'],
    #                              decstr(config[Dsn]['sshport']) if isdec else config[Dsn]['sshport'],
    #                              decstr(config[Dsn]['sshuser']) if isdec else config[Dsn]['sshuser'],
    #                              decstr(config[Dsn]['sshpassword'] if isdec else config[Dsn]['sshpassword'])
    #                     )
    #         return di
    #     except KeyError as e:
    #         raise Exception("配置文件'%s'中没有找到相应的配置信息:%s"%(DB_info_path,e))
    #     except Exception as e:
    #         raise Exception(e)

# # 查询数据库，sqlite数据库使用这个方法
# class QueryDB():
#     """数据库查询类"""
#     def QuerySqlite(self,di, Sql): # 用
#         connection = sqlite3.connect(di.db)
#         cur = connection.cursor()
#         cur.execute(Sql)
#         data = list(cur.fetchall())
#         col_name_list = [tuple[0] for tuple in cur.description]
#         df = pd.DataFrame(data, columns=col_name_list)
#         connection.commit()
#         connection.close()
#         return df
#
#     def QueryError(self, di, Sql):
#         """如果不是Sqlite，抛出异常"""
#         raise Exception("未定义" + di.dbtype + "类型数据库的查询方法...")
#
#     def __call__(self, Dsn, Sql):  # __call__魔法函数，实现了对象可以像函数一样调用
#         """查询数据库
#         :param Dsn 配置文件中数据库链接字符串信息
#         :param Sql 需要查询的sql信息
#         """
#         di = DB_Info.Get_SqliteDB_Info(Dsn)
#         switcher = {'SQLITE': self.QuerySqlite}
#         func = switcher.get(di.dbtype.upper(), lambda x, y: self.QueryError(di, Sql))
#         return func(di, Sql)

# 查询数据库，当存在多个数据库时，需要定义多个数据库的查询方法
class QueryDB():
    def QuerySqlite(self,di, Sql): # 用
        connection = sqlite3.connect(di.db)
        cur = connection.cursor()
        cur.execute(Sql)
        data = list(cur.fetchall())
        col_name_list = [tuple[0] for tuple in cur.description]
        df = pd.DataFrame(data, columns=col_name_list)
        connection.commit()
        connection.close()
        return df

    def QueryMysql(self, di, Sql):
        """查询GBASE
        :param di DB_Info封装对象：dbtype, host, dbport ,dbuser ,dbpassword ,db
        :param Sql 需要查询的sql信息
        """
        connection = pymysql.connect(host=di.host,
                                    port=int(di.dbport),
                                    user=di.dbuser,
                                    password=di.dbpassword,
                                    db=di.db,
                                    charset='utf8mb4')
        cur = connection.cursor()
        cur.execute(Sql)
        data = list(cur.fetchall())
        col_name_list = [tuple[0] for tuple in cur.description]
        df = pd.DataFrame(data, columns=col_name_list)
        connection.commit()
        connection.close()
        return df

    def QuerySPARK(self, di, Sql):
        """查询SPARK
        :param di DB_Info封装对象：dbtype, host, dbport ,dbuser ,dbpassword ,db
        :param Sql 需要查询的sql信息
        """
        conn = hive.Connection(host=di.host,
                                port=di.dbport,
                                username=di.dbuser,
                                database=di.db)
        cur = conn.cursor()
        #解决查询只有200行的问题，在vmax中可能会出现
        # cur.execute("set spark.sql.thriftServer.limitCollectNumber=0")
        cur.execute(Sql)
        data = list(cur.fetchall())
        col_name_list = [tuple[0] for tuple in cur.description]
        df = pd.DataFrame(data, columns=col_name_list)
        conn.commit()
        conn.close()
        return df

    def QueryGbase(self, di, Sql):
        """查询GBASE
        :param di DB_Info封装对象：dbtype, host, dbport ,dbuser ,dbpassword ,db
        :param Sql 需要查询的sql信息
        """
        connection = pymysql.connect(host=di.host,
                                    port=int(di.dbport),
                                    user=di.dbuser,
                                    password=di.dbpassword,
                                    db=di.db,
                                    charset='utf8mb4')
        cur = connection.cursor()
        cur.execute(Sql)
        data = list(cur.fetchall())
        col_name_list = [tuple[0] for tuple in cur.description]
        df = pd.DataFrame(data, columns=col_name_list)
        connection.commit()
        connection.close()
        return df

    def QueryError(self, di, Sql):
        """如果不是Sqlite，抛出异常"""
        raise Exception("未定义" + di.dbtype + "类型数据库的查询方法...")

    def __call__(self, Dsn, Sql):  # __call__魔法函数，实现了对象可以像函数一样调用
        """查询数据库
        :param Dsn 配置文件中数据库链接字符串信息
        :param Sql 需要查询的sql信息
        """
        di = DB_Info.Get_DB_Info(Dsn)
        switcher = {'SQLITE': self.QuerySqlite,
                    'MYSQL': self.QueryMysql,
                    'GBASE': self.QueryGbase,
                    'SPARK': self.QuerySPARK}
        func = switcher.get(di.dbtype.upper(), lambda x, y: self.QueryError(di, Sql))
        return func(di, Sql)


# # 修改数据库，sqlite数据库使用这个方法
# class CommitDB():
#     def CommitSqlite(self, di, Sql):
#         is_suc = False
#         try:
#             connection = sqlite3.connect(di.db, check_same_thread=False)
#             cur = connection.cursor()
#             cur.execute(Sql)
#             # cur.execute('commit')
#             connection.commit()
#             connection.close()
#             is_suc = True
#         except Exception as e:
#             print(e)
#             is_suc = False
#         return is_suc
#
#     def QueryError(self, di, Sql):
#         """如果不是Sqlite，抛出异常"""
#         raise Exception("未定义" + di.dbtype + "类型数据库的提交方法...")
#
#     """用于create、insert、update、delete操作Sqlite数据库"""
#     def __call__(self,Dsn,Sql):
#         """ __call__魔法函数，实现了对象可以像函数一样调用
#         用于create、insert、update、delete操作Sqlite数据库
#         :param Dsn 配置文件中数据库链接字符串信息
#         :param Sql 需要查询的sql信息
#         """
#         di = DB_Info.Get_SqliteDB_Info(Dsn)
#         switcher = {'SQLITE': self.CommitSqlite}
#         func = switcher.get(di.dbtype.upper(), lambda x, y: self.QueryError(di, Sql))
#         return func(di, Sql)


# 修改数据库，当存在多个数据库时，需要定义多个数据库的查询方法
class CommitDB():
    def CommitSqlite(self, di, Sql):
        is_suc = False
        try:
            connection = sqlite3.connect(di.db, check_same_thread=False)
            cur = connection.cursor()
            cur.execute(Sql)
            # cur.execute('commit')
            connection.commit()
            connection.close()
            is_suc = True
        except Exception as e:
            print(e)
            is_suc = False
        return is_suc

    def CommitMysql(self, di, Sql):
        """用于create、insert、update、delete操作Mysql数据库"""
        is_suc = False
        try:
            connection = pymysql.connect(host=di.host,
                                        port=int(di.dbport),
                                        user=di.dbuser,
                                        password=di.dbpassword,
                                        db=di.db,
                                        charset='utf8mb4')
            cur = connection.cursor()
            cur.execute(Sql)
            cur.execute('commit')
            connection.close()
            is_suc = True
        except Exception as e:
            print(e)
            is_suc = False
        return is_suc

    def CommitGBase(self, di, Sql):
        """用于create、insert、update、delete操作GBase数据库"""

        """用于create、insert、update、delete操作GBase数据库
        :param Dsn 配置文件中数据库链接字符串信息
        :param Sql 需要查询的sql信息
        """
        is_suc = False
        try:
            connection = pymysql.connect(host=di.host,
                                        port=int(di.dbport),
                                        user=di.dbuser,
                                        password=di.dbpassword,
                                        db=di.db,
                                        charset='utf8mb4')
            cur = connection.cursor()
            cur.execute(Sql)
            cur.execute('commit')
            connection.close()
            is_suc = True
        except Exception as e:
            print(e)
            is_suc = False
        return is_suc

    def CommitSPARK(self, di, Sql):
        """用于create、insert、update、delete操作Hive数据库"""
        is_suc = False
        try:
            # 创建与 hive 的连接
            conn = hive.Connection(host=di.host, port=int(di.dbport), username=di.dbuser)
            # 创建一个游标对象
            cursor = conn.cursor()
            # 执行 SQL 查询
            cursor.execute(Sql)
            # 提交事务
            conn.commit()
            # 关闭连接
            cursor.close()
            conn.close()
            is_suc = True
        except Exception as e:
            print(e)
            is_suc = False
        return is_suc

    def QueryError(self, di, Sql):
        """如果不是Sqlite，抛出异常"""
        raise Exception("未定义" + di.dbtype + "类型数据库的提交方法...")

        """用于create、insert、update、delete操作Sqlite数据库"""
    def __call__(self,Dsn,Sql):
        """ __call__魔法函数，实现了对象可以像函数一样调用
        用于create、insert、update、delete操作Sqlite数据库
        :param Dsn 配置文件中数据库链接字符串信息
        :param Sql 需要查询的sql信息
        """
        di = DB_Info.Get_DB_Info(Dsn)
        switcher = {'SQLITE': self.CommitSqlite,
                    'MYSQL': self.CommitMysql,
                    'GBASE': self.CommitGBase,
                    'SPARK': self.CommitSPARK}
        func = switcher.get(di.dbtype.upper(), lambda x, y: self.QueryError(di, Sql))
        return func(di, Sql)

def selectTable(Dsn, Sql):
    """查询数据库
    :param Dsn 配置文件中数据库链接字符串信息
    :param Sql 需要查询的sql信息
    """
    return QueryDB()(Dsn, Sql)

def modify_table(Dsn, Sql): # 用
    """用于create、insert、update、delete操作Sqlite数据库
    :param Dsn 配置文件中数据库链接字符串信息
    :param Sql 需要查询的sql信息
    """
    return CommitDB()(Dsn, Sql)